R Notebook

library(tidyverse)

Registered S3 methods overwritten by 'dbplyr':
  method         from
  print.tbl_lazy     
  print.tbl_sql      
── Attaching packages ──────────────────────── tidyverse 1.3.1 ──
✔ ggplot2 3.3.6     ✔ purrr   0.3.4
✔ tibble  3.1.7     ✔ dplyr   1.0.9
✔ tidyr   1.2.0     ✔ stringr 1.4.0
✔ readr   2.1.2     ✔ forcats 0.5.1
── Conflicts ─────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()

library(scales)

Attaching package: ‘scales’

The following object is masked from ‘package:purrr’:

    discard

The following object is masked from ‘package:readr’:

    col_factor

library(plotly)

Registered S3 method overwritten by 'data.table':
  method           from
  print.data.table     
Registered S3 method overwritten by 'htmlwidgets':
  method           from         
  print.htmlwidget tools:rstudio

Attaching package: ‘plotly’

The following object is masked from ‘package:ggplot2’:

    last_plot

The following object is masked from ‘package:stats’:

    filter

The following object is masked from ‘package:graphics’:

    layout

library(lubridate)

Attaching package: ‘lubridate’

The following objects are masked from ‘package:base’:

    date, intersect, setdiff, union

Beds

beds <- read_csv("raw_data/non_covid_raw_data/beds_by_nhs_board_of_treatment_and_specialty.csv") %>% janitor::clean_names()

Warning: One or more parsing issues, see `problems()` for detailsRows: 30458 Columns: 20── Column specification ─────────────────────────────────────────
Delimiter: ","
chr (10): Quarter, QuarterQF, HB, HBQF, Location, LocationQF,...
dbl  (5): AllStaffedBeddays, TotalOccupiedBeddays, AverageAva...
lgl  (5): AllStaffedBeddaysQF, TotalOccupiedBeddaysQF, Averag...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

beds %>% 
  filter(specialty_name == "All Acute") %>% 
  ggplot(aes(x = quarter, y = percentage_occupancy))+
  geom_line(aes(colour = hb), group = 1)+
  facet_wrap(~ hb)

beds <- beds %>% 
  select(-c(2,4,6,8,10,12,14,16,18,20)) %>% 
  filter(!hb %in% c("SB0801", "S92000003"))

beds <- beds %>% 
  filter(!hb %in% c("SB0801", "S92000003")) %>% 
  filter(hb == location)
  
# beds %>% 
# count(specialty_name)

a_e_beds <- beds %>% 
  filter(specialty_name == "Accident & Emergency")

# a&e percentage occupancy by hb over time
a_e_beds %>% 
  group_by(quarter, hb) %>% 
  summarise(mean_perc_occ = mean(percentage_occupancy, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = mean_perc_occ))+
  geom_line(aes(group = hb, colour = hb))+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

a_e_beds %>% 
  count(hb)

#all time bed occupancy percentage for health boards
a_e_beds %>% 
  group_by(quarter, hb) %>% 
  summarise(mean_perc_occ = mean(percentage_occupancy, na.rm = TRUE)) %>% 
ungroup() %>% 
  group_by(hb) %>% 
  summarise(avg_per_occ_all_time = mean(mean_perc_occ)) %>% 
  arrange(desc(avg_per_occ_all_time)) %>% 
  ggplot(aes(x = hb, y = avg_per_occ_all_time))+
  geom_col()+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

# workout the ten largest 
ten_largest_specialities <- beds %>%
  group_by(specialty_name) %>% 
  summarise(mean_avail_staffed_beds = mean(average_available_staffed_beds)) %>% 
  arrange(desc(mean_avail_staffed_beds)) %>% 
  slice_max(mean_avail_staffed_beds, n=10) %>% 
  select(1) %>% 
  pull()



# bed percentage availablity for top ten largest specialities
beds %>%
  filter(specialty_name %in% ten_largest_specialities) %>% 
  group_by(quarter, specialty_name) %>%
  summarise(mean_perc_occ = mean(percentage_occupancy)) %>% 
  ggplot(aes(x = quarter, y = mean_perc_occ))+
  geom_line(aes(colour = specialty_name, group = specialty_name))+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

  
# bed percentage availablity for all acute
  beds %>%
  filter(specialty_name == "All Acute") %>% 
  group_by(quarter, specialty_name) %>%
  summarise(mean_perc_occ = mean(percentage_occupancy)) %>% 
  ggplot(aes(x = quarter, y = mean_perc_occ))+
  geom_line(aes(colour = specialty_name, group = specialty_name))+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

  
  # bed percentage availability for intensive care
beds %>%
  filter(specialty_name == "Intensive Care Medicine") %>% 
  group_by(quarter, specialty_name) %>%
  summarise(mean_perc_occ = mean(percentage_occupancy)) %>% 
  ggplot(aes(x = quarter, y = mean_perc_occ))+
  geom_line(aes(colour = specialty_name, group = specialty_name))+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

NA
NA
NA

a_e_beds %>% 
  group_by(quarter) %>% 
  summarise(mean_available_beds = mean(average_available_staffed_beds, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = mean_available_beds))+
  geom_line(group ="1")+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45))

---

age sex

age_sex <- read_csv("raw_data/non_covid_raw_data/inpatient_and_daycase_by_nhs_board_of_treatment_age_and_sex.csv") %>% janitor::clean_names()

Rows: 129393 Columns: 18── Column specification ─────────────────────────────────────────
Delimiter: ","
chr (12): Quarter, QuarterQF, HB, HBQF, Location, LocationQF,...
dbl  (6): Episodes, LengthOfEpisode, AverageLengthOfEpisode, ...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

health_board_names <- read_csv("raw_data/non_covid_raw_data/health_board_names.csv")

Rows: 18 Columns: 5── Column specification ─────────────────────────────────────────
Delimiter: ","
chr (3): HB, HBName, Country
dbl (2): HBDateEnacted, HBDateArchived
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

age_sex %>% 
  count(hb)

season_age_sex <- age_sex %>% 
  mutate(date = yq(quarter),
         year = year(date),
         month = month(date, label = TRUE, abbr = FALSE),
         season = case_when(
           str_detect(month, "December") ~ "Winter",
           str_detect(month, "January") ~ "Winter",
           str_detect(month, "February") ~ "Winter",
           str_detect(month, "March") ~ "Spring",
           str_detect(month, "April") ~ "Spring",
           str_detect(month, "May") ~ "Spring",
           str_detect(month, "June") ~ "Summer",
           str_detect(month, "July") ~ "Summer",
           str_detect(month, "August") ~ "Summer",
           str_detect(month, "September") ~ "Autumn",
           str_detect(month, "October") ~ "Autumn",
           str_detect(month, "November") ~ "Autumn"),
         season = factor(season, order = TRUE)) 

library(lubridate)
library(zoo)

Attaching package: ‘zoo’

The following objects are masked from ‘package:base’:

    as.Date, as.Date.numeric

# change quarter column into the date at the start of each quarter
 age_sex <-  age_sex %>% 
    mutate(quarter = yq(quarter))

 # shows the total length of stay by age bracket for emergency inpatients
age_sex %>% 
  filter(admission_type == "Emergency Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(total_length_of_stay = sum(length_of_stay)) %>% 
  ggplot(aes(x = quarter, y = total_length_of_stay))+
  geom_line(aes(colour = age))+
  theme(axis.text.x = element_text(angle = 45))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

age_sex %>% 
count(admission_type)

age_sex

 # shows the total length of stay by age bracket for elective inpatients
age_sex %>% 
  filter(admission_type == "Elective Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(total_length_of_stay = sum(length_of_stay)) %>% 
  ggplot(aes(x = quarter, y = total_length_of_stay))+
  geom_line(aes(colour = age))+
  theme(axis.text.x = element_text(angle = 45))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

 # shows the mean length of stay by age bracket for emergency inpatients
#can facet by sex also if required
age_sex %>% 
  filter(admission_type == "Emergency Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(avg_length_of_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_length_of_stay))+
  geom_line(aes(colour = age, group = age))+
  theme(axis.text.x = element_text(angle = 45))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

  #facet_wrap( ~ sex)

 # shows the mean length of stay by age bracket for elective inpatients
# can facet by sex 
age_sex %>% 
  filter(admission_type == "Elective Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(avg_length_of_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_length_of_stay))+
  geom_line(aes(colour = age, group = age))+
  theme(axis.text.x = element_text(angle = 45))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

#   #facet_wrap(~ sex)

age_sex %>% 
  mutate(date = yq(quarter),
         year = year(date),
         month = month(date, label = TRUE, abbr = FALSE),
         season = case_when(
           str_detect(month, "December") ~ "Winter",
           str_detect(month, "January") ~ "Winter",
           str_detect(month, "February") ~ "Winter",
           str_detect(month, "March") ~ "Spring",
           str_detect(month, "April") ~ "Spring",
           str_detect(month, "May") ~ "Spring",
           str_detect(month, "June") ~ "Summer",
           str_detect(month, "July") ~ "Summer",
           str_detect(month, "August") ~ "Summer",
           str_detect(month, "September") ~ "Autumn",
           str_detect(month, "October") ~ "Autumn",
           str_detect(month, "November") ~ "Autumn"),
         season = factor(season, order = TRUE)) 

Warning: All formats failed to parse. No formats found.

age_sex %>% 
  filter(admission_type == "Emergency Inpatients") %>% 
  group_by(quarter, sex) %>% 
  summarise(avg_length_of_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_length_of_stay))+
  geom_line(aes(colour = sex, group = sex))+
  theme(axis.text.x = element_text(angle = 45))+
  geom_smooth(aes(colour = sex, group = sex), se = FALSE)+
  geom_point(aes(colour = sex), size = 0.5)+
  labs(title = "Emergency Inpatient by gender and average length of stay")

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

age_sex %>% 
  filter(admission_type == "Elective Inpatients") %>% 
  group_by(quarter, sex) %>% 
  summarise(avg_length_of_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_length_of_stay))+
  geom_line(aes(colour = sex, group = sex))+
  theme(axis.text.x = element_text(angle = 45))+
  geom_smooth(aes(colour = sex, group = sex), se = FALSE)+
  geom_point(aes(colour = sex), size = 0.5)+
  labs(title = "Elective Inpatient by gender and average length of stay")

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

# emergency inpatient by age and avg length of stay
age_sex %>% 
  filter(admission_type == "Emergency Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(avg_length_of_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_length_of_stay))+
  geom_line(aes(colour = age, group = age))+
  theme(axis.text.x = element_text(angle = 45))+
  #geom_smooth(aes(colour = sex, group = sex), se = FALSE)+
  geom_point(aes(colour = age), size = 0.5)+
  labs(title = "Emergency inpatient by age and avg length of stay")

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

# elective inpatient by age and avg length of stay
age_sex %>% 
  filter(admission_type == "Elective Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(avg_length_of_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_length_of_stay))+
  geom_line(aes(colour = age, group = age))+
  theme(axis.text.x = element_text(angle = 45))+
  #geom_smooth(aes(colour = sex, group = sex), se = FALSE)+
  geom_point(aes(colour = age), size = 0.5)+
  labs(title = "Elective inpatient by age and avg length of stay")

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

# emergency inpatient by age and avg length of epsiode
age_sex %>% 
  filter(admission_type == "Emergency Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(avg_length_of_episode = mean(average_length_of_episode, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_length_of_episode))+
  geom_line(aes(colour = age, group = age))+
  theme(axis.text.x = element_text(angle = 45))+
  #geom_smooth(aes(colour = sex, group = sex), se = FALSE)+
  geom_point(aes(colour = age), size = 0.5)+
  labs(title = "Emergency inpatient by age and avg length of episode")

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

# elective inpatient by age and avg length of episode
age_sex %>% 
  filter(admission_type == "Elective Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(avg_length_of_episode = mean(average_length_of_episode, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_length_of_episode))+
  geom_line(aes(colour = age, group = age))+
  theme(axis.text.x = element_text(angle = 45))+
  #geom_smooth(aes(colour = sex, group = sex), se = FALSE)+
  geom_point(aes(colour = age), size = 0.5)+
  labs(title = "Elective inpatient by age and avg length of episode")

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

# emergency inpatient by age and avg episodes
age_sex %>% 
  filter(admission_type == "Emergency Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(avg_episodes = mean(episodes, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_episodes))+
  geom_line(aes(colour = age, group = age))+
  theme(axis.text.x = element_text(angle = 45))+
  #geom_smooth(aes(colour = sex, group = sex), se = FALSE)+
  geom_point(aes(colour = age), size = 0.5)+
  labs(title = "Emergency inpatient by age and avg episodes")

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

# emergency inpatient by age and avg episodes
age_sex %>% 
  filter(admission_type == "Elective Inpatients") %>% 
  group_by(quarter, age) %>% 
  summarise(avg_episodes = mean(episodes, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_episodes))+
  geom_line(aes(colour = age, group = age))+
  theme(axis.text.x = element_text(angle = 45))+
  #geom_smooth(aes(colour = sex, group = sex), se = FALSE)+
  geom_point(aes(colour = age), size = 0.5)+
  labs(title = "Elective inpatient by age and avg episodes")

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

# Plot comparison of Emergency vs Elective submissions
age_sex %>% 
  filter(admission_type %in% c("Emergency Inpatients", "Elective Inpatients")) %>% 
  group_by(quarter, admission_type) %>% 
  summarise(avg_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_stay, colour = admission_type))+
  geom_line(aes(group = admission_type))+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

# avg_stay by admission type
age_sex %>% 
  group_by(quarter, admission_type) %>% 
  summarise(avg_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_stay, colour = admission_type))+
  geom_line(aes(group = admission_type))+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

# avg_stay for all types
age_sex %>% 
  group_by(quarter) %>% 
  summarise(avg_stay = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_stay, group = 1))+
  geom_line(aes(group = 1))+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

# number of stays for all types
age_sex %>% 
  group_by(quarter) %>% 
  summarise(total_stays = sum(stays, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = total_stays, group = 1))+
  geom_line(aes(group = 1))+
  geom_point()+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

simd <- read_csv("raw_data/non_covid_raw_data/inpatient_and_daycase_by_nhs_board_of_treatment_and_simd.csv") %>% janitor::clean_names()

Rows: 40821 Columns: 18── Column specification ─────────────────────────────────────────
Delimiter: ","
chr (11): Quarter, QuarterQF, HB, HBQF, Location, LocationQF,...
dbl  (7): SIMD, Episodes, LengthOfEpisode, AverageLengthOfEpi...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

#total episodes(hospitalisations?) by simd value
simd %>% 
  drop_na(simd) %>%
  mutate(simd = as.factor(simd)) %>% # gives each simd a separate colour
  group_by(quarter, simd) %>% 
  summarise(total_episodes = sum(episodes, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = total_episodes, group = simd))+
  geom_line(aes(colour = simd))+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))+
  scale_y_continuous(labels = scales::comma)

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

  
#avg number of episodes(hospitalisations?) by simd value
simd %>% 
  drop_na(simd) %>%
  mutate(simd = as.factor(simd)) %>% # gives each simd a separate colour
  group_by(quarter, simd) %>% 
  summarise(avg_episodes = mean(episodes, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_episodes, group = simd))+
  geom_line(aes(colour = simd))+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))+
  scale_y_continuous(labels = scales::comma)

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

#avg stays by simd value
simd %>% 
  drop_na(simd) %>%
  mutate(simd = as.factor(simd)) %>% # gives each simd a separate colour
  group_by(quarter, simd) %>% 
  summarise(avg_stays = mean(stays, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_stays, group = simd))+
  geom_line(aes(colour = simd))+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))+
  scale_y_continuous(labels = scales::comma)

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

NA

#total episodes(hospitalisations?) by simd value
simd %>% 
  drop_na(simd) %>%
  filter(admission_type == "Emergency Inpatients") %>% 
  mutate(simd = as.factor(simd)) %>% # gives each simd a separate colour
  group_by(quarter, simd) %>% 
  summarise(total_episodes = sum(episodes, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = total_episodes, group = simd))+
  geom_line(aes(colour = simd))+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))+
  scale_y_continuous(labels = scales::comma)

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

  
#avg number of episodes(hospitalisations?) by simd value
simd %>% 
  drop_na(simd) %>%
  filter(admission_type == "Emergency Inpatients") %>% 
  mutate(simd = as.factor(simd)) %>% # gives each simd a separate colour
  group_by(quarter, simd) %>% 
  summarise(avg_episodes = mean(episodes, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_episodes, group = simd))+
  geom_line(aes(colour = simd))+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))+
  scale_y_continuous(labels = scales::comma)

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

#avg stays by simd value
simd %>% 
  drop_na(simd) %>%
  filter(admission_type == "Emergency Inpatients") %>% 
  mutate(simd = as.factor(simd)) %>% # gives each simd a separate colour
  group_by(quarter, simd) %>% 
  summarise(avg_stays = mean(stays, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_stays, group = simd))+
  geom_line(aes(colour = simd))+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))+
  scale_y_continuous(labels = scales::comma)

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

NA
NA

# plot avg stay length for most and least deprived for emergency unpatients
simd %>% 
  filter(admission_type == "Emergency Inpatients", simd %in% c(1,5)) %>% 
  drop_na(simd) %>% 
  group_by(quarter,simd) %>% 
  summarise(avg_stay_length = mean(average_length_of_stay, na.rm = TRUE)) %>% 
  ggplot(aes(x = quarter, y = avg_stay_length)) +
  geom_line(aes(colour = simd, group = simd))+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

`summarise()` has grouped output by 'quarter'. You can override using the `.groups` argument.

NA

speciality <- read_csv("raw_data/non_covid_raw_data/inpatient_and_daycase_by_nhs_board_of_treatment_and_specialty.csv") %>% janitor::clean_names()

Rows: 95270 Columns: 18── Column specification ─────────────────────────────────────────
Delimiter: ","
chr (12): Quarter, QuarterQF, HB, HBQF, Location, LocationQF,...
dbl  (6): Episodes, LengthOfEpisode, AverageLengthOfEpisode, ...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

speciality %>% 
  count(admission_type)

speciality %>% 
  count(hb)

speciality %>% 
  count(location)

speciality %>% 
  count(specialty_name)

# add averages 
speciality_averages <- speciality %>% 
  group_by(quarter, admission_type) %>% 
  mutate(avg_length_spell= mean(average_length_of_spell, na.rm = TRUE),
         avg_length_episode = mean(average_length_of_episode, na.rm = TRUE)) %>% 
  ungroup()


speciality_averages %>% 
  group_by(quarter, admission_type) %>% 
  slice(1) %>% 
  ggplot(aes(x = quarter, y = average_length_of_episode, group = admission_type)) + 
  geom_line(aes(colour = admission_type))+
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

ae_wait_times <- read_csv("raw_data/non_covid_raw_data/monthly_ae_waitingtimes_202206.csv") %>% janitor::clean_names()

Rows: 15837 Columns: 25── Column specification ─────────────────────────────────────────
Delimiter: ","
chr (13): Country, HBT, TreatmentLocation, DepartmentType, Nu...
dbl (12): Month, NumberOfAttendancesAggregate, NumberOfAttend...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

#glimpse(ae_wait_times)


#make a date and year column with the first date of every month
ae_wait_times <- ae_wait_times %>% 
  mutate(date = ym(month), .after = month,
         year = year(date))

#make a percent column with percent of patients meeting the 4hr target time
ae_wait_times <- ae_wait_times %>% 
  mutate(percent_4hr_target_achieved = (number_meeting_target_aggregate/number_of_attendances_aggregate)*100) %>% 
  #add an 8hr one - not currently used
mutate(percent_seen_within_8hr = ((number_of_attendances_aggregate-attendance_greater8hrs)/number_of_attendances_aggregate)*100)

# draw percentage of 4 hour wait for all years
for_plotly <- ae_wait_times %>% 
  filter(department_type == "Emergency Department") %>% 
  group_by(date, department_type) %>% 
  summarise(avg_4hr_target_made = mean(percent_4hr_target_achieved)) %>% 
  ggplot(aes(x = date, y = avg_4hr_target_made))+
  geom_line(aes(colour = department_type))+
  scale_x_date(date_breaks = "6 months", date_labels =  "%b %Y")+
  theme(axis.text.x = element_text(angle = 90, hjust = 1, size =7))+
  geom_smooth()+
  geom_vline(xintercept = as.numeric(as.Date("2008-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2009-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2010-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2011-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2012-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2013-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2014-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2015-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2016-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2017-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2018-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2019-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2020-01-01")), linetype=4)+
  labs(title = "percentage of A&E departments meeting the 4 hr target turnaround for patients",
       subtitle = "added in vertical lines for January to help")

`summarise()` has grouped output by 'date'. You can override using the `.groups` argument.

ggplotly(for_plotly)

`geom_smooth()` using method = 'loess' and formula 'y ~ x'
Warning: `gather_()` was deprecated in tidyr 1.2.0.
Please use `gather()` instead.

# 4hr wait by health board for all years facet wrapped
ae_wait_times %>% 
  filter(department_type == "Emergency Department") %>% 
  group_by(date, hbt) %>% 
  mutate(avg_4hr_target_made = mean(percent_4hr_target_achieved)) %>% 
  slice(1) %>%  
  ggplot(aes(x = date, y = avg_4hr_target_made))+
  geom_line(aes(colour = hbt))+
  scale_x_date(date_breaks = "6 months", date_labels =  "%b %Y")+
  theme(axis.text.x = element_text(angle = 90, hjust = 1, size =7))+
  geom_smooth()+
  geom_vline(xintercept = as.numeric(as.Date("2008-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2009-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2010-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2011-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2012-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2013-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2014-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2015-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2016-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2017-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2018-01-01")), linetype=4)+
  geom_vline(xintercept = as.numeric(as.Date("2019-01-01")), linetype=4)+
  facet_wrap(~ hbt)

target_2007 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = mean(percent_4hr_target_achieved, na.rm = TRUE)) %>% 
  filter(year == 2007) %>% 
  rename(ae_target_2007 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2007)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2008 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = mean(percent_4hr_target_achieved, na.rm = TRUE)) %>% 
  filter(year == 2008) %>% 
  rename(ae_target_2008 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2008)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2009 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = mean(percent_4hr_target_achieved, na.rm = TRUE)) %>% 
  filter(year == 2009) %>% 
  rename(ae_target_2009 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2009)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2010 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = mean(percent_4hr_target_achieved, na.rm = TRUE)) %>% 
  filter(year == 2010) %>% 
  rename(ae_target_2010 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2010)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2011 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = mean(percent_4hr_target_achieved, na.rm = TRUE)) %>% 
  filter(year == 2011) %>% 
  rename(ae_target_2011 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2011)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2012 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = mean(percent_4hr_target_achieved, na.rm = TRUE)) %>% 
  filter(year == 2012) %>% 
  rename(ae_target_2012 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2012)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2013 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = mean(percent_4hr_target_achieved, na.rm = TRUE)) %>% 
  filter(year == 2013) %>% 
  rename(ae_target_2013 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2013)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2014 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = mean(percent_4hr_target_achieved, na.rm = TRUE)) %>% 
  filter(year == 2014) %>% 
  rename(ae_target_2014 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2014)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2015 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = mean(percent_4hr_target_achieved, na.rm = TRUE)) %>% 
  filter(year == 2015) %>% 
  rename(ae_target_2015 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2015)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2016 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = mean(percent_4hr_target_achieved, na.rm = TRUE)) %>% 
  filter(year == 2016) %>% 
  rename(ae_target_2016 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2016)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2017 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = mean(percent_4hr_target_achieved, na.rm = TRUE)) %>% 
  filter(year == 2017) %>% 
  rename(ae_target_2017 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2017)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2018 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = mean(percent_4hr_target_achieved, na.rm = TRUE)) %>% 
  filter(year == 2018) %>% 
  rename(ae_target_2018 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2018)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2019 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = mean(percent_4hr_target_achieved, na.rm = TRUE)) %>% 
  filter(year == 2019) %>% 
  rename(ae_target_2019 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2019)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2020 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = mean(percent_4hr_target_achieved, na.rm = TRUE)) %>% 
  filter(year == 2020) %>% 
  rename(ae_target_2020 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2020)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

target_2021 <- ae_wait_times %>%
  group_by(year, hbt) %>% 
  summarise(ae_4hr_target_achieved = mean(percent_4hr_target_achieved, na.rm = TRUE)) %>% 
  filter(year == 2021) %>% 
  rename(ae_target_2021 = ae_4hr_target_achieved) %>% 
  ungroup() %>% 
  select(hbt,ae_target_2021)

`summarise()` has grouped output by 'year'. You can override using the `.groups` argument.

ggplot(data = scotland) +
geom_sf(fill = "green")+
ggrepel::geom_text_repel(aes(x = lat , y = long, label = paste(HBCode, HBName, sep = "\n")), min.segment.length = 0.05,size = 2, color = "black", fontface = "bold") +
  theme_void()

library(sf)

scotland_smaller <- scotland %>% # make a smaller version for performance issues
  st_simplify(TRUE, dTolerance = 2000)
#fixes problems caused by above 
scotland_smaller <- sf::st_cast(scotland_smaller, "MULTIPOLYGON")


# 
#   fig <- ggplotly(
#     ggplot(scotland)+
#   geom_sf(aes(fill = HBName))
# )
#   fig

  
  p <- ggplot(scotland_smaller) + 
  geom_sf(aes(fill = HBName, text = paste("<b>", HBName, "</b>\n", HBCode)))+
    theme_void()

Warning: Ignoring unknown aesthetics: text

  p %>%
  ggplotly(tooltip = "text") %>%
  style(hoverlabel = list(bgcolor = "white"), hoveron = "fill")

NA
NA

---

covid_age_sex <- read_csv("raw_data/covid_raw_data/hospital_admissions_hb_agesex_20220302.csv")

Rows: 43516 Columns: 12── Column specification ─────────────────────────────────────────
Delimiter: ","
chr (8): HB, HBQF, AgeGroup, AgeGroupQF, Sex, SexQF, Admissio...
dbl (4): WeekEnding, NumberAdmissions, Average20182019, Perce...
ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

head(covid_age_sex)

library(leaflet)

data(quakes)

# Show first 20 rows from the `quakes` dataset
leaflet(data = quakes[1:20,]) %>% addTiles() %>%
  addMarkers(~long, ~lat, popup = ~as.character(mag))